import sqlite3
import statistics
import time
import numpy as np

from openpyxl import load_workbook


class Sqlite(object):

    def __init__(self):
        self.con = sqlite3.connect("Device1.db")
        self.cur = self.con.cursor()

    def get_device_info(self):
        wb = load_workbook(r'host.xlsx')
        ws = wb['Switch']
        return [(ws.cell(i + 1, 1).value, ws.cell(i + 1, 2).value, ws.cell(i + 1, 3).value, ws.cell(i + 1, 4).value,
                 ws.cell(i + 1, 5).value, ws.cell(i + 1, 6).value) for i in range(1, ws.max_row)]

    def create_device_table(self):
        create_sql = """CREATE TABLE "t_device" (
        "d_id"	INTEGER NOT NULL UNIQUE,
        "hostname"	TEXT,
        "ip"	TEXT NOT NULL UNIQUE,
        "user"	TEXT NOT NULL DEFAULT 'admin',
        "passwd"	TEXT NOT NULL,
        "company"	TEXT NOT NULL DEFAULT 'Huawei',
        "software"	TEXT,
        PRIMARY KEY("d_id" AUTOINCREMENT)
    )"""
        with self.con:
            try:
                self.cur.execute(create_sql)
                # con.commit()
                print("数据表创建成功！")
            except:
                print("数据表创建失败！")

    def insert_device(self):
        sql = """insert into t_device (hostname,ip,user,passwd,company,software) values (?,?,?,?,?,?)"""
        with self.con:
            try:
                self.cur.executemany(sql, self.get_device_info())
                print("插入设备信息成功!")
            except:
                print("插入设备信息失败!")

    def sql_execute(self, sql):
        with self.con:
            try:
                return self.cur.execute(sql)
            except:
                print(f"{sql}执行失败！")

    def update_device_info(self):
        ip = input("输入需要修改设备IP地址：")
        user = input("输入修改的登录账号：")
        passwd = input("请输入修改的密码：")
        sql = f"""UPDATE t_device SET user = '{user}',passwd = '{passwd}' WHERE ip = '{ip}';"""
        self.sql_execute(sql)
        print(f"{ip}更新成功！")

    def delete_device_info(self):
        ip = input("输入需要删除的设备IP地址：")
        sql = f"""DELETE FROM t_device WHERE ip = '{ip}';"""
        self.sql_execute(sql)
        print(f"{ip}删除成功！")

    def select_device_info(self):
        sql = "SELECT * FROM t_device;"
        return self.sql_execute(sql)

    def sql_to_dict(self):
        return [{"hostname": i[1],
                 "ip": i[2],
                 "user": i[3],
                 "passwd": i[4],
                 "company": i[5],
                 "version": i[6]}
                for i in self.select_device_info()]

def main():
    start = time.time()
    i = 1
    li = []
    device_list = Sqlite().sql_to_dict()
    while i <= 100000:
        start1 = time.time()
        for y in device_list:
            print(y)
        end1 = time.time()
        print(end1 - start1)
        li.append(end1 - start1)
        print(i)
        i += 1
    end = time.time()
    print("总耗时：", end - start)
    print("平均耗时：", np.mean(li))


if __name__ == '__main__':
    main()
